<cfcomponent output="false">
	
	<!--- --->
	
	<cffunction name="getEvents" access="public" output="true" returntype="query">
		<cfargument name="startdate" required="true" />
		<cfargument name="enddate" required="true" />
		<cfargument name="isGoogle" required="false" default="" />
		<cfargument name="googleEventID" required="false" default="" />
		<cfargument name="googleEventIDLike" required="false" default="" />
		<cfargument name="userID" required="false" default="#request.session.user.id#" />
		<cfargument name="isChild" required="false" default="false" />
		<cfargument name="modified_after" required="false" default="" />
		<cfargument name="active" required="false" default="1" />

		<cfset var local = {} />

		<cfquery name="local.getEvents" datasource="#$.site.getVar('datasource')#">
			select 
			hex(event.id) as id,
			event.description,
			date_format(event.start,'%m/%d/%Y') as start_date,
			date_format(event.stop,'%m/%d/%Y') as stop_date,
			date_format(event.start,'%H:%i:%s') as start_time,
			date_format(event.stop,'%H:%i:%s') as stop_time,
			event.category,
			event.google_event_id,
			event.google_calendar_id,
			event.google_calendar_link,
			hex(event.parent_id) as parent_id,
			event.child_events.count as child_events,
			user.event_categories,
			event.modified,
			event.active
			from event
			left join user on user.id = event.user_id
			left join (
				select count(event.id) as count,
				event.parent_id
				from event
				where event.active = 1
				group by event.parent_id
			) child_events on child_events.parent_id = event.id
			where 1=1
			and event.user_id = #$.database.uuid(arguments.userID)#
			<cfif arguments.startdate neq "">
				and event.start >= #$.database.datetime(arguments.startdate)#
			</cfif>
			<cfif arguments.enddate neq "">
				and event.start < #$.database.datetime(arguments.enddate)#
			</cfif>
			<cfif arguments.isGoogle eq "true">
				and event.google_event_id is not null
			</cfif>
			<cfif arguments.isGoogle eq "false">
				and event.google_event_id is null
			</cfif>
			<cfif arguments.googleEventID neq "">
				and event.google_event_id = #$.database.string(arguments.googleEventID)#
			</cfif>
			<cfif arguments.googleEventIDLike neq "">
				and event.google_event_id like '%#arguments.googleEventIDLike#%'
			</cfif>
			<cfif arguments.isChild eq true>
				and event.parent_id is not null
			</cfif>
			<cfif arguments.modified_after neq "">
				and event.modified >= #$.database.datetime(arguments.modified_after)#
			</cfif>
			<cfif arguments.active neq "">
				and event.active = #$.database.string(arguments.active)#
			</cfif>
			order by event.start asc,event.created desc
		</cfquery>
		
		<cfreturn local.getEvents />
		
	</cffunction>
	
	<!--- --->

	<cffunction name="getSecretaryEvents" access="public" output="true" returntype="query">
		<cfargument name="targetDateTimeUTC" required="false" default="#now()#" />
		<cfargument name="userID" required="false" default="#request.session.user.id#" />

		<cfset var local = {} />

		<cfset local.targetDateTimeLocal = $.date.applyLocalization(arguments.targetDateTimeUTC) />
		<cfset local.targetDateLocal = dateFormat(local.targetDateTimeLocal, "mm/dd/yyyy") />
		<cfset local.targetWeekday = dayOfWeek(local.targetDateTimeLocal) - 1 />

		<cfset local.nextDayLocal = dateAdd("d", 1, dateFormat(local.targetDateTimeLocal, "mm/dd/yyyy")) />
		<cfset local.nextDayUTC = $.date.localToUTC(local.nextDayLocal) />

		<!--- Timezone delta --->
		<cfset local.delta = listGetAt(request.session.user.timezone, 1, ":") />

		<!--- Timezone dst --->
		<cfset local.year = year(local.targetDateTimeLocal) />
		<cfset local.dst = 0 />
		<cfif request.session.user.dst eq 1>
			<cfif structKeyExists(application.DST,"start_#local.year#")>
				<cfset local.dst_start = application.dst['start_#local.year#'] & ' 02:00:00' />
				<cfset local.dst_end = application.dst['stop_#local.year#'] & ' 02:00:00' />
			<cfelse>
				<cfset local.dst_start = application.dst['start_2014'] & ' 02:00:00' />
				<cfset local.dst_end = application.dst['stop_2014'] & ' 02:00:00' />
			</cfif>

			<cfif dateCompare(local.targetDateTimeLocal, local.dst_start) gte 0 and dateCompare(local.targetDateTimeLocal, local.dst_end) eq -1>
				<cfset local.dst = 1 />
			</cfif>
		</cfif>

		<cfquery name="local.getSecretaryEvents" datasource="#$.site.getVar('datasource')#">
			select
			'calendar_event' as event_type,
			hex(event.id) as id,
			event.description,
			date_add(date_add(event.start, interval #$.database.int(local.delta)# hour), interval #$.database.int(local.dst)# hour) as local_start,
			date_add(date_add(event.stop, interval #$.database.int(local.delta)# hour), interval #$.database.int(local.dst)# hour) as local_end
			from event
			where event.active = 1
			and event.user_id = #$.database.uuid(arguments.userID)#
			and ((event.start >= #$.database.datetime(arguments.targetDateTimeUTC)#
					and event.start < #$.database.datetime(local.nextDayUTC)#) /* Will start later today */
				or (event.start < #$.database.datetime(arguments.targetDateTimeUTC)#
					and event.stop > #$.database.datetime(arguments.targetDateTimeUTC)#)) /* Already started, but is not yet finished */
			union all
			select
			'workweek_event' as event_type,
			hex(we.id) as id,
			we.name,
			we.modified_start_time as local_start,
			date_add(we.modified_start_time, interval we.duration minute) as local_end
			from (
				select
				workweek_event.id,
				workweek_event.name,
				case
					when workweek_event.weekday = #$.database.int(local.targetWeekday)# then timestamp(#$.database.datetime(local.targetDateLocal)#, time(workweek_event.start_time))
					when workweek_event.weekday < #$.database.int(local.targetWeekday)# then timestamp(addDate(#$.database.datetime(local.targetDateLocal)#, workweek_event.weekday - #$.database.int(local.targetWeekday)#), time(workweek_event.start_time))
					else timestamp(addDate(#$.database.datetime(local.targetDateLocal)#, workweek_event.weekday - #$.database.int(local.targetWeekday)# - 7), time(workweek_event.start_time))
				end as modified_start_time,
				workweek_event.duration
				from workweek_event
				where workweek_event.active = 1
					and workweek_event.user_id = #$.database.uuid(arguments.userID)#
					and workweek_event.start_time is not null
			) we
			where ((we.modified_start_time >= #$.database.datetime(local.targetDateTimeLocal)#
					and we.modified_start_time < #$.database.datetime(local.nextDayLocal)#) /* Will start later today */
				or (we.modified_start_time < #$.database.datetime(local.targetDateTimeLocal)#
					and date_add(we.modified_start_time, interval we.duration minute) > #$.database.datetime(local.targetDateTimeLocal)#)) /* Already started, but is not yet finished */
			order by local_start asc
		</cfquery>

		<cfreturn local.getSecretaryEvents />

	</cffunction>

	<!--- --->

</cfcomponent>